package com.mtu.parser;

import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import java.io.IOException;
import java.io.File;
import java.sql.*;
import java.io.*;
//import java.util.Iterator;

/**
 * Example program to list tables from a URL and storing each table in an array named arrOfTables[]
 * Then the data of first table is stored in an array named personalInfo[]
 * This code can be further improved by removing hard coding in some cases. 
 */

public class TableParserFile2
{
	public static void main(String[] args) throws IOException, SQLException, ClassNotFoundException 
	{
		int i=0,j=0;
		
		File input=new File("B.Tech. First Year Result 2010-11.htm");
    	Document doc = Jsoup.parse(input, "UTF-8");
 
		Elements table = doc.select("table");
        
    	int size=table.size();
    	//System.out.println(size);
        
    	Element [] arrOfTables = new Element[size];
    	
    	for (Element src : table) 
        {
    		arrOfTables[i]=src;     
    		i++;		
        }
    	i=0;
        //System.out.println(arrOfTables[4].text());
    	
    	
    	
    	
    	
    	/* This section of code scraps the personal information
    	 * The personal information is stored in the arrOfTables[0]
    	 * Then student's name, father's name etc are extracted*/
    	
    	Elements firstTable=arrOfTables[0].select("td");
    	//System.out.println(firstTable);
    	
    	int firstTableSize=firstTable.size();
    	//System.out.println(firstTableSize);
    	
    	String[] personalInfo= new String[firstTableSize];
    	
    	for (Element src : firstTable) 
        {
    		personalInfo[j]=src.text().trim();   //Data is stored from tableData[0] onwards.  
    		j++;		
        }
    	j=0;
    	//System.out.println(personalInfo[1]);
    	
    	String name=null, fatherName=null, motherName=null, rollNo=null, enrolmentNo=null, status=null, branch=null, college=null;
    	
    	for(i=0;i<firstTableSize;i=i+2)
    	{
    		if (personalInfo[i].equalsIgnoreCase("Student's Name :"))
    		{
    			name=personalInfo[i+1];
    			continue;
    		}
    		
    		if(personalInfo[i].equalsIgnoreCase("Father's Name :"))
    		{
    			fatherName=personalInfo[i+1];
    			continue;
    		}
    		
    		if(personalInfo[i].equalsIgnoreCase("Mother's Name :"))
    		{
    			motherName=personalInfo[i+1];
    			continue;
    		}
    		
    		if(personalInfo[i].equalsIgnoreCase("Roll No. :"))
    		{
    			rollNo=personalInfo[i+1];
    			continue;
    		}
    		
    		if(personalInfo[i].equalsIgnoreCase("Enrolment No. :"))
    		{
    			enrolmentNo=personalInfo[i+1];
    			continue;
    		}
    		
    		if(personalInfo[i].equalsIgnoreCase("Status :"))
    		{
    			status=personalInfo[i+1];
    			continue;
    		}
    		
    		if(personalInfo[i].equalsIgnoreCase("Course/Branch :"))
    		{
    			branch=personalInfo[i+1];
    			continue;
    		}
    		
    		if(personalInfo[i].equalsIgnoreCase("Institute Name :"))
    		{
    			college=personalInfo[i+1];
    			continue;
    		}
    	}
    	
    	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		Connection con = DriverManager.getConnection("jdbc:odbc:mydsn","system","oracle");
		PreparedStatement stmt = con.prepareStatement("insert into studentdetails10 values(?,?,?,?,?,?,?,?)");
		stmt.setString(1,rollNo);
		stmt.setString(2,name);
		stmt.setString(3,fatherName);
		stmt.setString(4,motherName);
		stmt.setString(5,enrolmentNo);
		stmt.setString(6,status);
		stmt.setString(7,branch);
		stmt.setString(8,college);
		stmt.executeUpdate();
		con.commit();
		con.close();
		
    	System.out.println("Name= "+name);	
    	System.out.println("Father's Name= "+fatherName);
    	System.out.println("Mother's Name= "+motherName);
    	System.out.println("Roll no= "+rollNo);
    	System.out.println("Enrolment No= "+enrolmentNo);
    	System.out.println("Status= "+status);
    	System.out.println("Branch= "+branch);
    	System.out.println("College= "+college);
    	
    	
    	
    	
    	
    	
    	
    	
    	/* This section of code stores the marks of the odd semester in the array named oddMarksInfo[]
         * This code also takes care that the empty entries in the table; eg --- are not entered in the arrays
         * The gp marks are also stored in the externalMarks[] only and not in the internalMarks[]
         * Number of subjects are taken as 11  
         */
    	
    	System.out.println();
    	System.out.println();
    	//System.out.println("MARKS DETAILS ODD SEMESTER");
    	
    	Elements thirdTable=arrOfTables[2].select("td");
    	//System.out.println(thirdTable);
    	
    	int thirdTableSize=thirdTable.size();
    	//System.out.println(thirdTableSize);
    	
    	String[] oddMarksInfo=new String[thirdTableSize];
    	for (Element src : thirdTable) 
        {
    		oddMarksInfo[j]=src.text().trim();   //Data is stored from tableData[0] onwards.  
    		j++;		
        }
    	j=0;
    	//System.out.println(oddMarksInfo[9]);
    	
    	
    	int oddNoOfSubjects=12;
    	String[] oddSubjectName= new String[oddNoOfSubjects];
    	int[] oddExternalMarks = new int[oddNoOfSubjects];
    	int[] oddInternalMarks = new int[oddNoOfSubjects];
    	int[] oddCarryOverMarks = new int[oddNoOfSubjects];
    	int[] oddCredit = new int[oddNoOfSubjects];
    	int oddPositionCounter=0;
    	
    	for(i=6;i<thirdTableSize;i=i+5)
    	{
    		if(oddMarksInfo[i].substring(0,2).equalsIgnoreCase("gp"))
    		{
    			oddSubjectName[oddPositionCounter]=oddMarksInfo[i];
    			oddExternalMarks[oddPositionCounter]=Integer.parseInt(oddMarksInfo[i+1]);
    			oddInternalMarks[oddPositionCounter]=0;
    			oddCarryOverMarks[oddPositionCounter]=0;
    			oddCredit[oddPositionCounter]=0;
    			
    			oddPositionCounter++;
    			continue;
    		}
    		
    		if(oddMarksInfo[i+1].equalsIgnoreCase("---") && oddMarksInfo[i+2].equalsIgnoreCase("---"))
    		{
    			oddSubjectName[oddPositionCounter]=oddMarksInfo[i];
        		oddExternalMarks[oddPositionCounter]=0;
        		oddInternalMarks[oddPositionCounter]=0;
        		if(oddMarksInfo[i+3].equals(""))
    				oddCarryOverMarks[oddPositionCounter]=0;
    			else
    				oddCarryOverMarks[oddPositionCounter]=Integer.parseInt(oddMarksInfo[i+3]);
        		
        		if(oddMarksInfo[i+4].equals(""))
        			oddCredit[oddPositionCounter]=0;
        		else
    				oddCredit[oddPositionCounter]=Integer.parseInt(oddMarksInfo[i+4]);
            		
        		oddPositionCounter++;
        		continue;
    		}
        	
    		else if(!(oddMarksInfo[i+1].equalsIgnoreCase("---")) && oddMarksInfo[i+2].equalsIgnoreCase("---"))
    		{
    			oddSubjectName[oddPositionCounter]=oddMarksInfo[i];
        		oddExternalMarks[oddPositionCounter]=Integer.parseInt(oddMarksInfo[i+1]);
        		oddInternalMarks[oddPositionCounter]=0;
        		
        		if(oddMarksInfo[i+3].equals(""))
    				oddCarryOverMarks[oddPositionCounter]=0;
    			else
    				oddCarryOverMarks[oddPositionCounter]=Integer.parseInt(oddMarksInfo[i+3]);
        		
        		if(oddMarksInfo[i+4].equals(""))
        			oddCredit[oddPositionCounter]=0;
        		else
    				oddCredit[oddPositionCounter]=Integer.parseInt(oddMarksInfo[i+4]);
            		
        		oddPositionCounter++;
        		continue;
    		}
    		
    		else if(oddMarksInfo[i+1].equalsIgnoreCase("---") && !(oddMarksInfo[i+2].equalsIgnoreCase("---")))
    		{
    			oddSubjectName[oddPositionCounter]=oddMarksInfo[i];
        		oddExternalMarks[oddPositionCounter]=0;
        		oddInternalMarks[oddPositionCounter]=Integer.parseInt(oddMarksInfo[i+2]);
        		
        		if(oddMarksInfo[i+3].equals(""))
    				oddCarryOverMarks[oddPositionCounter]=0;
    			else
    				oddCarryOverMarks[oddPositionCounter]=Integer.parseInt(oddMarksInfo[i+3]);
        		
        		if(oddMarksInfo[i+4].equals(""))
        			oddCredit[oddPositionCounter]=0;
        		else
    				oddCredit[oddPositionCounter]=Integer.parseInt(oddMarksInfo[i+4]);
            		
        		oddPositionCounter++;
        		continue;
    		}
    		
    		else
    		{
    			oddSubjectName[oddPositionCounter]=oddMarksInfo[i];
        		oddExternalMarks[oddPositionCounter]=Integer.parseInt(oddMarksInfo[i+1]);
        		oddInternalMarks[oddPositionCounter]=Integer.parseInt(oddMarksInfo[i+2]);
        		
        		if(oddMarksInfo[i+3].equals(""))
    				oddCarryOverMarks[oddPositionCounter]=0;
    			else
    				oddCarryOverMarks[oddPositionCounter]=Integer.parseInt(oddMarksInfo[i+3]);
        		
        		if(oddMarksInfo[i+4].equals(""))
        			oddCredit[oddPositionCounter]=0;
        		else
    				oddCredit[oddPositionCounter]=Integer.parseInt(oddMarksInfo[i+4]);
            		
        		oddPositionCounter++;
        		continue;
    		}
    	}
    	
    	/*System.out.println(oddSubjectName[11]);
    	System.out.(oddExternalMarks[11]);
    	System.out.println(oddInternalMarks[11]);
    	System.out.println(oddCarryOverMarks[11]);
    	System.out.println(oddCredit[11]);*/
    	
    	System.out.print("Subject Code\t External Marks\t Sessional Marks\t Credit");
    	System.out.println();
    	
    	/*try {
    	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		Connection con = DriverManager.getConnection("jdbc:odbc:mydsn","system","oracle");
		PreparedStatement stmt = con.prepareStatement("insert into 1SEMMARKSDETAILS10-11 values(?,?,?,?,?,?)");
		//System.out.println(stmt.get);
		
		//stmt.setS
		stmt.setString(1,rollNo);
		stmt.setString(2,oddSubjectName[0]);
		stmt.setInt(3,oddExternalMarks[0]);
		stmt.setInt(4,oddInternalMarks[0]);
		stmt.setInt(5,oddCarryOverMarks[0]);
		stmt.setInt(6,oddCredit[0]);
		stmt.execute();
		con.commit();
		con.close();
    	}
    	catch(Exception e) {
    		e.printStackTrace();
    	}*/
		
    	for(i=0;i<oddNoOfSubjects;i++)
    	{	
    		System.out.print(oddSubjectName[i]+"\t");
        	System.out.print(oddExternalMarks[i]+"\t");
        	System.out.print(oddInternalMarks[i]+"\t");
        	System.out.print(oddCarryOverMarks[i]+"\t");
        	System.out.print(oddCredit[i]+"\t");
        	System.out.println();
    	}
    	
    	
    	
    	
    	
    	
    	
    	
    	
    	
    	
    	
    	
    	
    	/* This section of code stores the marks of the even semester in the array named evenMarksInfo[]
         * This code also takes care that the empty entries in the table; eg --- are not entered in the arrays
         * The gp marks are also stored in the externalMarks[] only and not in the internalMarks[]
         * Number of subjects are taken as 11  
         */
    	
    	System.out.println();
    	System.out.println();
    	System.out.println("MARKS DETAILS EVEN SEMESTER");
    	
    	
    	Elements fourthTable=arrOfTables[3].select("td");
    	//System.out.println(fourthTable);
    	
    	int fourthTableSize=fourthTable.size();
    	//System.out.println(fourthTableSize);
    	
    	String[] evenMarksInfo=new String[fourthTableSize];
    	for (Element src : fourthTable) 
        {
    		evenMarksInfo[j]=src.text().trim();   //Data is stored from tableData[0] onwards.  
    		j++;		
        }
    	//System.out.println(evenMarksInfo[5]);
    	
    	int evenNoOfSubjects=11;
    	String[] evenSubjectName= new String[evenNoOfSubjects];
    	int[] evenExternalMarks = new int[evenNoOfSubjects];
    	int[] evenInternalMarks = new int[evenNoOfSubjects];
    	int[] evenCarryOverMarks = new int[evenNoOfSubjects];
    	int[] evenCredit = new int[evenNoOfSubjects];
    	int evenPositionCounter=0;
    	
    	for(i=6;i<fourthTableSize;i=i+5)
    	{
    		if(evenMarksInfo[i].substring(0,2).equalsIgnoreCase("gp"))
    		{
    			evenSubjectName[evenPositionCounter]=evenMarksInfo[i];
    			evenExternalMarks[evenPositionCounter]=Integer.parseInt(evenMarksInfo[i+1]);
    			evenInternalMarks[evenPositionCounter]=0;
    			evenCarryOverMarks[evenPositionCounter]=0;
    			evenCredit[evenPositionCounter]=0;
    			
    			evenPositionCounter++;
    			continue;
    		}
    		
    		if(evenMarksInfo[i+1].equalsIgnoreCase("---") && evenMarksInfo[i+2].equalsIgnoreCase("---"))
    		{
    			evenSubjectName[evenPositionCounter]=evenMarksInfo[i];
    			evenExternalMarks[evenPositionCounter]=0;
    			evenInternalMarks[evenPositionCounter]=0;
        		if(evenMarksInfo[i+3].equals(""))
        			evenCarryOverMarks[evenPositionCounter]=0;
    			else
    				evenCarryOverMarks[evenPositionCounter]=Integer.parseInt(evenMarksInfo[i+3]);
        		
        		if(evenMarksInfo[i+4].equals(""))
        			evenCredit[evenPositionCounter]=0;
        		else
        			evenCredit[evenPositionCounter]=Integer.parseInt(evenMarksInfo[i+4]);
            		
        		evenPositionCounter++;
        		continue;
    		}
        	
    		else if(!(evenMarksInfo[i+1].equalsIgnoreCase("---")) && evenMarksInfo[i+2].equalsIgnoreCase("---"))
    		{
    			evenSubjectName[evenPositionCounter]=evenMarksInfo[i];
    			evenExternalMarks[evenPositionCounter]=Integer.parseInt(evenMarksInfo[i+1]);
    			evenInternalMarks[evenPositionCounter]=0;
        		
        		if(evenMarksInfo[i+3].equals(""))
        			evenCarryOverMarks[evenPositionCounter]=0;
    			else
    				evenCarryOverMarks[evenPositionCounter]=Integer.parseInt(evenMarksInfo[i+3]);
        		
        		if(evenMarksInfo[i+4].equals(""))
        			evenCredit[evenPositionCounter]=0;
        		else
        			evenCredit[evenPositionCounter]=Integer.parseInt(evenMarksInfo[i+4]);
            		
        		evenPositionCounter++;
        		continue;
    		}
    		
    		else if(evenMarksInfo[i+1].equalsIgnoreCase("---") && !(evenMarksInfo[i+2].equalsIgnoreCase("---")))
    		{
    			evenSubjectName[evenPositionCounter]=evenMarksInfo[i];
    			evenExternalMarks[evenPositionCounter]=0;
    			evenInternalMarks[evenPositionCounter]=Integer.parseInt(evenMarksInfo[i+2]);
        		
        		if(evenMarksInfo[i+3].equals(""))
        			evenCarryOverMarks[evenPositionCounter]=0;
    			else
    				evenCarryOverMarks[evenPositionCounter]=Integer.parseInt(evenMarksInfo[i+3]);
        		
        		if(evenMarksInfo[i+4].equals(""))
        			evenCredit[evenPositionCounter]=0;
        		else
        			evenCredit[evenPositionCounter]=Integer.parseInt(evenMarksInfo[i+4]);
            		
        		evenPositionCounter++;
        		continue;
    		}
    		
    		else
    		{
    			evenSubjectName[evenPositionCounter]=evenMarksInfo[i];
    			evenExternalMarks[evenPositionCounter]=Integer.parseInt(evenMarksInfo[i+1]);
    			evenInternalMarks[evenPositionCounter]=Integer.parseInt(evenMarksInfo[i+2]);
        		
        		if(evenMarksInfo[i+3].equals(""))
        			evenCarryOverMarks[evenPositionCounter]=0;
    			else
    				evenCarryOverMarks[evenPositionCounter]=Integer.parseInt(evenMarksInfo[i+3]);
        		
        		if(evenMarksInfo[i+4].equals(""))
        			oddCredit[evenPositionCounter]=0;
        		else
        			evenCredit[evenPositionCounter]=Integer.parseInt(evenMarksInfo[i+4]);
            		
        		evenPositionCounter++;
        		continue;
    		}
    	}
    	
    	/*System.out.println(evenSubjectName[11]);
    	System.out.println(evenExternalMarks[11]);
    	System.out.println(evenInternalMarks[11]);
    	System.out.println(evenCarryOverMarks[11]);
    	System.out.println(evenCredit[11]);*/
    	
    	System.out.print("Subject Code\t External Marks\t Sessional Marks\t Credit");
    	System.out.println();
    	for(i=0;i<evenNoOfSubjects;i++)
    	{
    		System.out.print(evenSubjectName[i]+"\t");
        	System.out.print(evenExternalMarks[i]+"\t");
        	System.out.print(evenInternalMarks[i]+"\t");
        	System.out.print(evenCarryOverMarks[i]+"\t");
        	System.out.print(evenCredit[i]+"\t");
        	System.out.println();
    	}   	
	}
}

    	

    	